package com.telit.common.util;

import com.telit.common.exception.ForestBaseException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.Base64Utils;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * Create by Chencf on 2019/03/21
 * Excel下载工具类
 */
public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    //2003+ 版本的excel
    private final static String excel2003L =".xls";

    //2007+ 版本的excel
    private final static String excel2007U =".xlsx";

    /**
     * 创建excel文档，
     * @param list 数据
     * @param keys list中map的key数组集合，用于从map中取值
     * @param columnNames excel的列名，用于生成列名
     * @param head 总标题名称
     * */
    public static Workbook createWorkBook(List<? extends Object> list,String []keys,String columnNames[],String head) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页）
        Sheet sheet = wb.createSheet();

        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth(i, (int) (35.7 * 150));
        }
        int rowIndex=0;

        // 创建第一行
        Row row = sheet.createRow(rowIndex);


        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        CellStyle headcss = wb.createCellStyle();
        DataFormat dataFormat = wb.createDataFormat();
        cs.setDataFormat(dataFormat.getFormat("@")); // 设置为文本格式
        cs2.setDataFormat(dataFormat.getFormat("@")); // 设置为文本格式
        headcss.setDataFormat(dataFormat.getFormat("@")); // 设置为文本格式

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);


        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.getIndex());

        headcss.setFont(f);
        headcss.setBorderLeft(BorderStyle.THIN);
        headcss.setBorderRight(BorderStyle.THIN);
        headcss.setBorderTop(BorderStyle.THIN);
        headcss.setBorderBottom(BorderStyle.THIN);
        headcss.setAlignment(HorizontalAlignment.CENTER);   //居中对齐

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(BorderStyle.THIN);                 //细边线
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);        //居中对齐

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setAlignment(HorizontalAlignment.CENTER);
        //设置为文本格式


        //设置标题
        if (StringUtils.hasText(head)) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, columnNames.length - 1);
            sheet.addMergedRegion(cellRangeAddress);
            Cell cells = row.createCell(0);
            cells.setCellType(CellType.STRING);
            cells.setCellStyle(headcss);
            cells.setCellValue(head);
            for (int i = 1; i < columnNames.length; i++) {
                Cell cl = row.createCell((short) i);
                cl.setCellValue("");
                cl.setCellType(CellType.STRING);
                cl.setCellStyle(headcss);
            }
            rowIndex++;
            row = sheet.createRow((short) rowIndex);
        }

        //设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
            cell.setCellType(CellType.STRING);
        }
        rowIndex++;
        //设置每行每列的值
        for (int i = 0; i <list.size(); i++) {
            Object o = list.get(i);
            if (o==null) {
                continue;
            }

            Map data = null;
            if (o instanceof Map) {
                data = (Map) o;
            } else {
                data = getValue(o);
            }

            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            Row row1 = sheet.createRow((short) rowIndex);
            // 在row行上创建一个方格
            for(short j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                Object value = data.get(keys[j]);
                cell.setCellValue(value == null ? "" : value.toString());
                cell.setCellStyle(cs2);
                cell.setCellType(CellType.STRING);
            }
            rowIndex++;
        }
        return wb;
    }

    /**
     * 创建excel文件
     */
    public static void createFile(List<Map<String, Object>> list,String []keys,String columnNames[],String headName,File file)  throws IOException {
        if (file==null) {
            //throw new BaseException(CommonResultCode.ERR, "文件不存在");
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ExcelUtil.createWorkBook(list, keys, columnNames, headName).write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(file);
            os.writeTo(fos);
            fos.flush();
        } catch (IOException ioe) {
            ioe.printStackTrace();
        } finally {
            if (fos != null) {
                fos.close();
            }
        }
    }

    /**
     * 获得object的属性（字段名）
     * @param thisObj
     * @return
     */
    public static Map getValue(Object thisObj) {
        Map map = new HashMap();
        Class c;
        try {
            c = Class.forName(thisObj.getClass().getName());
            Method[] m = c.getMethods();
            for (int i = 0; i < m.length; i++) {
                String method = m[i].getName();
                if (method.startsWith("get")) {
                    try{
                        Object value = m[i].invoke(thisObj);
                        if (value != null) {
                            String key=method.substring(3);
                            key=key.substring(0,1).toLowerCase()+key.substring(1);
                            map.put(key, value);
                        }
                    }catch (Exception e) {
                        System.out.println("error:"+method);
                    }
                }
            }
        } catch (Exception e)        {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * Bean --> Map 1: 利用Introspector和PropertyDescriptor 将Bean --> Map
     * @param obj
     * @return
     */
    public static Map<String, Object> transBean2Map(Object obj) {
        if (obj == null) {
            return null;
        }
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            for (PropertyDescriptor property : propertyDescriptors) {
                String key = property.getName();
                // 过滤class属性
                if (!key.equals("class")) {
                    // 得到property对应的getter方法
                    Method getter = property.getReadMethod();
                    Object value = getter.invoke(obj);
                    map.put(key, value);
                }

            }
        } catch (Exception e) {
            logger.error("transBean2Map Error {}" ,e);
        }
        return map;
    }

    /**
     * 导出
     *
     * @param list        数据
     * @param columnNames 表头
     * @param keys
     * @param fileName
     * @param request
     * @param response
     */
    public static void download(List<? extends Object> list, String keys[], String columnNames[], String head, String fileName, HttpServletRequest request, HttpServletResponse response) {

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ExcelUtil.createWorkBook(list, keys, columnNames, "").write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");

        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {

            // 根据浏览器，设置文件名
            String userAgent = request.getHeader("user-agent");
            if (userAgent.contains("Firefox")) {
                fileName = "=?UTF-8?B?" + Base64Utils.encodeToString(fileName.getBytes("UTF-8")) + "?=";
            } else {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            ServletOutputStream out = response.getOutputStream();

            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int length;
            while ((length = bis.read(buff)) != -1) {
                bos.write(buff, 0, length);
            }
        } catch (final IOException e) {
            e.printStackTrace();
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * Excel导入
     * @param in       Excel文件流
     * @param fileName Excel文件名称，因为要根据文件名称的后缀.xls .xlsx来决定采用哪种解析方式
     * @param startColumn 从哪一列开始解析数据，从0开始，如果不传递此参数，将自动从每一行的有效数据列开始解析
     * @param endColumn 每一行的数据，到哪一列解析结束（不包含此列），不足指定列的将补充空字符串，如果不传递此参数，每一行的数据将自动在有效数据列结束时停止解析
     */
    public static List<List<String>> getListByExcel(InputStream in, String fileName, Integer startColumn, Integer endColumn) throws Exception {
        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
          //  throw new BaseException(CommonResultCode.ERR, "Excel解析失败，工作簿为空");
        }
        Row row = null;
        Cell cell = null;
        List<List<String>> list = new ArrayList<List<String>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            Sheet sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            //遍历当前sheet中的所有行
            //包含头部,用于校验
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                //读取一行
                row = sheet.getRow(j);
                //去掉空行
                if (row == null) {
                    continue;
                }
                // 封装每一行中的所有列，如果传递的参数不为空，将从指定的列起始值处开始取固定列的内容
                // 这种方式得到的数据能够保证每一行的长度都是固定的
                List<String> li = new ArrayList<String>();
                int startNumber = startColumn == null ? sheet.getRow(0).getFirstCellNum() : startColumn;
                int endNumber = endColumn == null ? sheet.getRow(0).getLastCellNum() : endColumn;
                for (int y = startNumber; y < endNumber; y++) {
                    cell = row.getCell(y);
                    if (cell==null){
                        li.add("");
                    }else {
                        li.add(cell.toString());
                    }
                }
                list.add(li);
            }
        }
        return list;
    }

    /**
     * Excel导入重载
     */
    public static List<List<String>> getListByExcel(InputStream in, String fileName) throws Exception {
        return getListByExcel(in, fileName, null, null);
    }
    /**
     * 根据文件后缀，自适应上传文件的版本
     */
    public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(excel2003L.equals(fileType)){
            //2003- 版本的excel
            wb = new HSSFWorkbook(inStr);
        }else if(excel2007U.equals(fileType)){
            //2007+ 版本的excel
            wb = new XSSFWorkbook(inStr);
        }else{
            //throw new BaseException(CommonResultCode.ERR, "Excel版本未知");
        }
        return wb;
    }

    /**
     * 对表格中数值进行格式化
     */
    /**
     * 对表格中数值进行格式化
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String value;
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getRichStringCellValue().getString().trim();
                break;
            case NUMERIC:
                //格式化日期
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = String.valueOf(cell.getNumericCellValue());
                } else if ("yyyy\"年\"m\"月\";@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/m;@".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");  //日期格式化
                    value = sdf2.format(cell.getDateCellValue());
                } else if ("yyyy/mm/dd;@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy\\/mm\\/dd;@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy\"年\"mm\"月\"dd\"日\";@".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
                    value = sdf.format(cell.getDateCellValue());
                } else if ("m-d-yy".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
                    value = sdf.format(cell.getDateCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
                    value = sdf.format(cell.getDateCellValue());
                } else if ("m/d".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");  //日期格式化
                    value = sdf2.format(cell.getDateCellValue());
                } else {
                    value = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue() + "";
                break;
            default:
                value = "";
                break;
        }
        return value;
    }

    /**
     * 下载static文件夹下的文件(文件要在static文件夹下)
     * @param fileName
     * @param response
     */
    public static void downLoadFileInStatic(HttpServletRequest request, HttpServletResponse response, String fileName) {
        if(org.apache.commons.lang3.StringUtils.isBlank(fileName)) {
            throw new ForestBaseException("文件名称不能为空");
        }

        response.setCharacterEncoding(request.getCharacterEncoding());
        response.setContentType("application/octet-stream");
        InputStream inputStream = null;
        try {
            fileName = new String(fileName.getBytes(), StandardCharsets.UTF_8);
            ClassPathResource classPathResource = new ClassPathResource("static"+File.separator+fileName);
            inputStream = classPathResource.getInputStream();
            try {
                IOUtils.copy(inputStream,response.getOutputStream());
                response.flushBuffer();
            }finally {
                IOUtils.closeQuietly(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
}
